#股票数据源  pip install lxml pandas requests bs4  解决StringIO的问题 tushare pip install "pandas<0.25.0"
import sys

import tushare as ts
import  pandas as pd
#数据库依赖包 安装用命令pip install  pymysql /pip install MySQLdb（Python2）
import pymysql
import requests
import time
#时间格式化方法
import datetime
from sqlalchemy import create_engine
import re
#判断编码格式的依赖--好像不是很好用
import chardet
#获取code信息
#请求数据
#解析数据
#入库
#step2 逐个获取股票数据
def getStockList():
    #print('getDataStock')
       # print(code)
        df = ts.get_stock_basics()
       #保存备份
        df.to_csv('d:/data/stock/get_stock_basics.csv')
        return df
def saveStockToDB():
    db = pymysql.Connect('localhost', 'root', 'abcde@124', 'test')
    # 创建游标
    cursor = db.cursor()
    sql = 'truncate table stock_info '
    cursor.execute(sql)
    db.commit()
        # 关闭游标
    cursor.close()
        # 关闭数据库
    db.close()
    path = 'd:/data/stock/get_stock_basics.csv'
    df = pd.read_csv(path, encoding="utf-8")
    #df = df[df.date > str(mdate)]
    # df['code'] = code
    for i in range(0, len(df)):
        df.loc[i, 'code'] =str(df.loc[i, 'code']).zfill(6)
    # df['nowdate'] = df['date']
    # df['nowprice'] = df['close']
    # df['nowrate'] = df['p_change']
    # dbs = df[['code', 'nowdate', 'nowprice', 'nowrate']]
    engine = create_engine("mysql+pymysql://{}:{}@{}/{}".format('root', 'abcde@124', 'localhost:3306', 'test'))
    df.to_sql(name='stock_info', con=engine, if_exists='append', index=False)

def getFundList():
    r0 = requests.get('http://fund.eastmoney.com/js/fundcode_search.js')
    ori = r0.text
    regx='\["(.+?)","(.+?)","(.+?)","(.+?)","(.+?)"\]'
    fundlist=re.findall(regx, ori);
    df = pd.DataFrame(fundlist, columns=['code', 'simpe', 'name','type','pinying'])
    df = df.set_index('code')
    # 保存备份
    df.to_csv('d:/data/stock/fund.csv')

def saveFundToDB():
    db = pymysql.Connect('localhost', 'root', 'abcde@124', 'test')
    # 创建游标
    cursor = db.cursor()
    sql = 'truncate table fund_info '
    cursor.execute(sql)
    db.commit()
        # 关闭游标
    cursor.close()
        # 关闭数据库
    db.close()
    path = 'd:/data/stock/fund.csv'
    df = pd.read_csv(path, encoding="utf-8")
    for i in range(0, len(df)):
        df.loc[i, 'code'] =str(df.loc[i, 'code']).zfill(6)
    dbs = df[['code', 'name', 'type']]
    engine = create_engine("mysql+pymysql://{}:{}@{}/{}".format('root', 'abcde@124', 'localhost:3306', 'test'))
    dbs.to_sql(name='fund_info', con=engine, if_exists='append', index=False)

def main():
    getStockList()
    saveStockToDB()
    print('stock finished!')
    getFundList()
    saveFundToDB()
    print('fund finished!')
main()
